use crate::{config::DbConfig, ReservationManager, Rsvp};
use abi::pb::reservation::{
    ListenRequest, ListenResponse, QueryRequest, Reservation, ReservationId, Status,
};
use abi::{error::Error, types::Validator};
use anyhow::Result;
use async_trait::async_trait;
use chrono::{DateTime, Utc};
use sqlx::{PgPool, Row, postgres::PgPoolOptions};
use sqlx::{postgres::types::PgRange, types::Uuid};

#[allow(unused)]
#[async_trait]
impl Rsvp for ReservationManager {
    async fn reserve(&self, mut reservation: Reservation) -> Result<Reservation, Error> {
        reservation.validate()?;

        let timespan: PgRange<DateTime<Utc>> = reservation.get_timespan().into();

        // i32 转 Status, 再实现display to_string写入数据库
        let status = Status::try_from(reservation.status).unwrap_or(Status::Pending);

        // to_string传入的是text类型, 需在sql中转为对应enum
        let id: Uuid = sqlx::query(
            "INSERT INTO rsvp.reservations (user_id, resource_id, timespan, status, note) VALUES ($1, $2, $3, $4::rsvp.reservation_status, $5) RETURNING id"
        )
        .bind(reservation.user_id.clone())
        .bind(reservation.resource_id.clone())
        .bind(timespan)
        .bind(status.to_string())
        .bind(reservation.note.clone())
        .fetch_one(&self.pool)
        .await?
        .get(0);

        reservation.id = id.to_string();

        Ok(reservation)
    }

    // 将pending状态的订单转成confirmed
    async fn confirm(&self, id: ReservationId) -> Result<Reservation, Error> {
        let id = Uuid::parse_str(&id.id).map_err(|_| Error::InvalidId("id", id.id))?;

        let rsvp: Reservation = sqlx::query_as(
            "UPDATE rsvp.reservations SET status = 'confirmed' WHERE id = $1 AND status = 'pending' RETURNING *",
        )
        .bind(id)
        .fetch_one(&self.pool)
        .await?;

        Ok(rsvp)
    }

    async fn update_note(&self, id: ReservationId, note: String) -> Result<Reservation, Error> {
        let id = Uuid::parse_str(&id.id).map_err(|_| Error::InvalidId("id", id.id))?;

        let rsvp: Reservation =
            sqlx::query_as("UPDATE rsvp.reservations SET note = $1 WHERE id = $2 RETURNING *")
                .bind(note)
                .bind(id)
                .fetch_one(&self.pool)
                .await?;

        Ok(rsvp)
    }

    async fn delete(&self, id: ReservationId) -> Result<(), Error> {
        let id = Uuid::parse_str(&id.id).map_err(|_| Error::InvalidId("id", id.id))?;

        sqlx::query("DELETE FROM rsvp.reservations WHERE id = $1")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    async fn query(&self, request: QueryRequest) -> Result<Vec<Reservation>, Error> {
        let status = Status::try_from(request.status).unwrap_or(Status::Pending);
        let timespan: PgRange<DateTime<Utc>> = request.get_timespan().into();

        let rsvps = sqlx::query_as(
            "SELECT * FROM rsvp.query($1, $2, $3, $4::rsvp.reservation_status, $5, $6, $7)",
        )
        .bind(str_to_option(&request.user_id))
        .bind(str_to_option(&request.resource_id))
        .bind(timespan)
        .bind(status.to_string())
        .bind(request.page)
        .bind(request.desc)
        .bind(request.page_size)
        .fetch_all(&self.pool)
        .await?;

        Ok(rsvps)
    }

    async fn listen(&self, request: ListenRequest) -> Result<Vec<ListenResponse>, Error> {
        todo!()
    }
}

// 将str用option包装, 用于query接口传入参数
fn str_to_option(s: &str) -> Option<&str> {
    if s.is_empty() { None } else { Some(s) }
}

impl ReservationManager {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    pub async fn from_config(config: &DbConfig) -> Result<Self, abi::error::Error> {
        let pool = PgPoolOptions::default()
            .max_connections(config.pool_size)
            .connect(&config.get_db_url())
            .await?;
        Ok(Self::new(pool))
    }
}

#[cfg(test)]
mod tests {

    use crate::QueryRequestBuilder;
    use abi::utils::convert_to_timestamp;
    use chrono::FixedOffset;
    use prost_types::Timestamp;

    use super::*;

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn reserver_should_work_for_valid_timespan() {
        let manager = ReservationManager::new(migrated_pool.clone());
        // 用户输入带时区的时间
        let start = "2025-05-04T14:51:56+0800"
            .parse::<DateTime<FixedOffset>>()
            .unwrap();
        let end = start + chrono::Duration::days(1);

        let r = Reservation {
            id: "0".to_string(),
            user_id: "sky".to_string(),
            resource_id: "room-1".to_string(),
            status: Status::Pending as i32,
            // TODO: Some() and .into()
            start: Some(convert_to_timestamp(start.with_timezone(&Utc))),
            end: Some(convert_to_timestamp(end.with_timezone(&Utc))),
            note: "this is a note".to_string(),
        };

        let result = manager.reserve(r).await.unwrap();
        assert!(result.id != "0");
    }

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn reserver_conflict_reservation_should_reject() {
        let manager = ReservationManager::new(migrated_pool.clone());

        let r1 = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );

        let r2 = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-03T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );

        assert!(manager.reserve(r1).await.is_ok());

        let err = manager.reserve(r2).await.err().unwrap();
        //assert_matches!(err, Error::DbConflictReservation());
        if let Error::DbConflictReservation(msg) = err {
            println!("err msg: {:?}", msg);
            assert!(true);
        } else {
            assert!(false);
        }
    }

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn comfire_reservation_should_work() {
        let manager = ReservationManager::new(migrated_pool.clone());

        let r = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );

        let r = manager.reserve(r).await.unwrap();
        let rid = ReservationId { id: r.id.clone() };
        let r = manager.confirm(rid).await.unwrap();
        assert_eq!(r.status, Status::Confirmed as i32);
    }

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn confire_not_pending_should_do_nothing() {
        let manager = ReservationManager::new(migrated_pool.clone());

        let r = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );

        let r = manager.reserve(r).await.unwrap();
        let rid = ReservationId { id: r.id.clone() };

        let r = manager.confirm(rid.clone()).await.unwrap();
        assert_eq!(r.status, Status::Confirmed as i32);

        // change status again should do nothing
        let ret = manager.confirm(rid.clone()).await.unwrap_err();
        assert_eq!(ret, abi::error::Error::DbNotFound);
    }

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn update_reservation_should_work() {
        let manager = ReservationManager::new(migrated_pool.clone());
        let r = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );
        let r = manager.reserve(r).await.unwrap();

        let rid = ReservationId { id: r.id.clone() };
        let r = manager
            .update_note(rid.clone(), "new note".to_string())
            .await
            .unwrap();
        assert_eq!(r.note, "new note");

        // update a non-exist reservation should do nothing
        let not_exist_id = ReservationId {
            id: Uuid::new_v4().to_string(),
        };
        let ret = manager
            .update_note(not_exist_id.clone(), "new note2".to_string())
            .await
            .unwrap_err();
        assert_eq!(ret, abi::error::Error::DbNotFound);
    }

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn delete_reservation_should_work() {
        let manager = ReservationManager::new(migrated_pool.clone());
        let r = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );
        let r = manager.reserve(r).await.unwrap();

        let rid = ReservationId { id: r.id.clone() };
        let result = manager.delete(rid.clone()).await;
        assert!(result.is_ok());
    }

    #[sqlx_database_tester::test(pool(variable = "migrated_pool", migrations = "../migrations"))]
    async fn query_reservation_should_work() {
        let manager = ReservationManager::new(migrated_pool.clone());
        let rsvp = Reservation::new_pending(
            "sky",
            "room-1",
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
        );
        let id = manager.reserve(rsvp).await.unwrap().id;

        let request = QueryRequest::new(
            "room-1",
            "sky",
            Status::Pending as i32,
            "2025-05-04T14:00:00+0800".parse().unwrap(),
            "2025-05-05T14:00:00+0800".parse().unwrap(),
            1,
            10,
            false,
        );
        let result = manager.query(request).await.unwrap();
        assert_eq!(result.len(), 1);

        // out of range
        let request = QueryRequestBuilder::default()
            .resource_id("room-1")
            .user_id("sky")
            .status(Status::Pending as i32)
            .start("2025-05-06T14:00:00+0800".parse::<Timestamp>().unwrap())
            .end("2025-05-07T14:00:00+0800".parse::<Timestamp>().unwrap())
            .build()
            .unwrap();
        let result = manager.query(request).await.unwrap();
        assert_eq!(result.len(), 0);

        let request = QueryRequestBuilder::default()
            .resource_id("room-1")
            .user_id("sky")
            .status(Status::Confirmed as i32)
            .start("2025-05-04T14:00:00+0800".parse::<Timestamp>().unwrap())
            .end("2025-05-05T14:00:00+0800".parse::<Timestamp>().unwrap())
            .build()
            .unwrap();

        let result = manager.query(request.clone()).await.unwrap();
        assert_eq!(result.len(), 0);

        let rid = ReservationId { id: id.clone() };
        manager.confirm(rid).await.unwrap();
        let result = manager.query(request).await.unwrap();
        assert_eq!(result.len(), 1);
    }
}
